package cn.sciento.transfer.infra.datasource.sql;


import cn.sciento.core.convertor.ApplicationContextHelper;
import cn.sciento.core.domain.Page;
import cn.sciento.core.exception.CommonException;
import io.choerodon.mybatis.pagehelper.PageHelper;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.session.TransactionIsolationLevel;
import cn.sciento.mybatis.DynamicSqlMapper;
import cn.sciento.transfer.infra.datasource.ddl.TableScriptBuilder;
import cn.sciento.transfer.infra.datasource.factory.TableScriptBuilderFactory;
import cn.sciento.transfer.infra.datasource.mybatis.DynamicSqlMapperTemplate;
import cn.sciento.transfer.infra.datasource.utils.TransferDataSourceTemplate;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.StatementCallback;
import org.springframework.jdbc.support.JdbcUtils;
import org.springframework.util.Assert;

public class SqlUtils {
  private static final Logger LOGGER = LoggerFactory.getLogger(SqlUtils.class);
  
  public static final int DEFAULT_FIRST_PAGE = 0;
  
  public static final int DEFAULT_PAGE_SIZE = 20;
  
  public static final String DB_SQL_SERVER = "SQLSERVER";
  
  public static final String DB_SQL_SERVER_PREFIX = "dbo";
  
  public static final String DB_CATALOG_DELIMITOR = ".";
  
  public static boolean checkTableExists(String dataSourceCode, String serviceName, String catalog, String tableName) {
    TransferDataSourceTemplate transferDataSourceTemplate = (TransferDataSourceTemplate)ApplicationContextHelper.getContext().getBean(TransferDataSourceTemplate.class);
    try {
      return ((Boolean)transferDataSourceTemplate.doExecuteInConnection(dataSourceCode, serviceName, catalog, connection -> {
            try {
              return Boolean.valueOf(checkTableExistsWithInOnceStatement(connection, tableName));
            } catch (Exception ex) {
              LOGGER.warn("check table exists with error: ", ex);
              return Boolean.valueOf(false);
            } 
          })).booleanValue();
    } catch (Exception ex) {
      LOGGER.warn("do execute in connection with error: ", ex);
      return false;
    } 
  }
  
  public static boolean checkTableExistsWithInOnceStatement(Connection connection, String tableName) {
    Assert.notNull(connection, "connection cannot be null.");
    Assert.notNull(tableName, "table name cannot be null.");
    try {
      executeWithInOnceStatement(connection, stmt -> Boolean.valueOf(stmt.execute(SqlBuilder.buildSelectCheckExists(null, tableName))));
      return true;
    } catch (Exception ex) {
      LOGGER.warn("check table exists with in once statement with error: ", ex);
      return false;
    } 
  }
  
  public static <T> Page<Map<String, Object>> doPageQuery(String dataSourceCode, String serviceName, String sql, T parameterValue, int page) {
    DynamicSqlMapperTemplate dynamicSqlMapperTemplate = (DynamicSqlMapperTemplate)ApplicationContextHelper.getContext().getBean(DynamicSqlMapperTemplate.class);
    return (Page<Map<String, Object>>)dynamicSqlMapperTemplate.doExecute(dataSourceCode, serviceName, TransactionIsolationLevel.REPEATABLE_READ, dynamicSqlMapper -> {
          PageHelper.startPage(page, 20);
          List<Map<String, Object>> result = dynamicSqlMapper.selectList(sql, parameterValue);
          return (result == null) ? new Page() : (Page)result;
        });
  }
  
  public static int[] doBatchUpdate(JdbcTemplate jdbcTemplate, String sql, final List<Map<String, Object>> batchDataList) {
    return jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
          public void setValues(PreparedStatement preparedStatement, int i) throws SQLException {
            Map<String, Object> currentRow = batchDataList.get(i);
            int parameterIndex = 0;
            for (Map.Entry<String, Object> entry : currentRow.entrySet()) {
              parameterIndex++;
              preparedStatement.setObject(parameterIndex, entry.getValue());
            } 
          }
          
          public int getBatchSize() {
            return batchDataList.size();
          }
        });
  }
  
  public static String buildQualifiedTableName(String dataSourceCode, String serviceName, String dbCatalog, String tableName) {
    TransferDataSourceTemplate transferDataSourceTemplate = (TransferDataSourceTemplate)ApplicationContextHelper.getContext().getBean(TransferDataSourceTemplate.class);
    return (String)transferDataSourceTemplate.doExecuteInConnection(dataSourceCode, serviceName, dbCatalog, connection -> buildQualifiedTableNameInConnection(connection, dbCatalog, tableName));
  }
  
  public static String buildQualifiedTableNameInConnection(Connection connection, String dbCatalog, String tableName) {
    try {
      String databaseProductName = connection.getMetaData().getDatabaseProductName().toUpperCase();
      if (databaseProductName.contains("SQLSERVER"))
        return dbCatalog + "." + "dbo" + "." + tableName; 
      return dbCatalog + "." + tableName;
    } catch (Exception ex) {
      LOGGER.error("build qualified table name with error: ", ex);
      return null;
    } 
  }
  
  public static String getTableInitDDL(String tableName, String initDsCode, String initDbCode, String serviceCode) {
    TransferDataSourceTemplate transferDataSourceTemplate = (TransferDataSourceTemplate)ApplicationContextHelper.getContext().getBean(TransferDataSourceTemplate.class);
    return (String)transferDataSourceTemplate.doExecuteInConnection(initDsCode, serviceCode, initDbCode, connection -> {
          try {
            String databaseProductName = connection.getMetaData().getDatabaseProductName().toLowerCase();
            TableScriptBuilder scriptBuilder = TableScriptBuilderFactory.getInstance().creator(databaseProductName);
            return scriptBuilder.getCreateTableScript(connection, tableName);
          } catch (Exception e) {
            LOGGER.error("获取建表脚本失败", e);
            return null;
          } 
        });
  }
  
  public static List<String> getTablePrimaryKey(String tableName, String initDsCode, String initDbCode, String serviceCode) {
    TransferDataSourceTemplate transferDataSourceTemplate = (TransferDataSourceTemplate)ApplicationContextHelper.getContext().getBean(TransferDataSourceTemplate.class);
    return (List<String>)transferDataSourceTemplate.doExecuteInConnection(initDsCode, serviceCode, initDbCode, connection -> {
          try {
            String databaseProductName = connection.getMetaData().getDatabaseProductName().toLowerCase();
            TableScriptBuilder scriptBuilder = TableScriptBuilderFactory.getInstance().creator(databaseProductName);
            return scriptBuilder.getTablePrimaryKey(connection, initDbCode, tableName);
          } catch (Exception e) {
            LOGGER.error("获取表主键失败", e);
            return null;
          } 
        });
  }
  
  public static <T> T executeWithInOnceStatement(Connection connection, StatementCallback<T> statementCallback) {
    Assert.notNull(statementCallback, "Callback object must not be null");
    Statement stmt = null;
    try {
      stmt = connection.createStatement();
      return executeWithInOnceStatementThenClose(stmt, statementCallback);
    } catch (SQLException ex) {
      LOGGER.error("execute with in once statement failed: ", ex);
      throw new CommonException(ex);
    } 
  }
  
  public static <T> T executeWithInOnceStatementThenClose(Statement stmt, StatementCallback<T> statementCallback) {
    Assert.notNull(stmt, "statement cannot be null");
    Assert.notNull(statementCallback, "Callback object must not be null");
    try {
      T result = (T)statementCallback.doInStatement(stmt);
      return result;
    } catch (SQLException ex) {
      JdbcUtils.closeStatement(stmt);
      stmt = null;
      throw new CommonException(ex);
    } finally {
      JdbcUtils.closeStatement(stmt);
    } 
  }
}
